--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';



/*
select SOURCE,port_cod,r.* from (
SELECT &outils.stragg(source) source,soc_cod, ets_cod, service_cod, prfarr_cod, prfcompta_cod, port_cod, destana_cod, cptcompte_no, unfab_cod, postana_cod, depot_cod, prefiban_no, rib_no, bic_no, reaautoofstrait_ok, delailiv_nb, delaitech_nb, delaitrp_nb, enlevementcli_ok, client_cod, cpt_soc_cod, cpt_ets_cod, orgafin_cod, orgafin_soc_cod, orgafin_ets_cod, enreg_id, devise_cod, vilbanque_lib, domibanque_lib, plafondcde_val, regimetva_cod, fournisseur_cod, modpai_cod, modreg_cod, foursoc_rang, enreg_s, consolidation_cptable_cod, dads_ok, axe_analytique_cod, niveau_analytique_cod, condition_groupe_ok, taxparafisc_coef, seuil_franco_val, seuil_cde_val, transit_ok, traitgeneetiq_typ, idemetsref_ok, jlivlu_ok, jlivma_ok, jlivme_ok, jlivje_ok, jlivve_ok, jlivsa_ok, ouv1_heur, ferm1_heur, ouv2_heur, ferm2_heur
FROM (
  SELECT 'uv' source ,g.* FROM se_ACH_FOURSOC g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ACH_FOURSOC g
  )
GROUP BY soc_cod, ets_cod, service_cod, prfarr_cod, prfcompta_cod, port_cod, destana_cod, cptcompte_no, unfab_cod, postana_cod, depot_cod, prefiban_no, rib_no, bic_no, reaautoofstrait_ok, delailiv_nb, delaitech_nb, delaitrp_nb, enlevementcli_ok, client_cod, cpt_soc_cod, cpt_ets_cod, orgafin_cod, orgafin_soc_cod, orgafin_ets_cod, enreg_id, devise_cod, vilbanque_lib, domibanque_lib, plafondcde_val, regimetva_cod, fournisseur_cod, modpai_cod, modreg_cod, foursoc_rang, enreg_s, consolidation_cptable_cod, dads_ok, axe_analytique_cod, niveau_analytique_cod, condition_groupe_ok, taxparafisc_coef, seuil_franco_val, seuil_cde_val, transit_ok, traitgeneetiq_typ, idemetsref_ok, jlivlu_ok, jlivma_ok, jlivme_ok, jlivje_ok, jlivve_ok, jlivsa_ok, ouv1_heur, ferm1_heur, ouv2_heur, ferm2_heur
HAVING Count(1)!=2) r
ORDER BY fournisseur_cod*/

DELETE FROM  se_ACH_FOURSOC ;

--SELECT * FROM &erp.ACH_FOURNISSEUR where fournisseur_cod not in(select fournisseur_cod from &erp.ach_foursoc)
--SELECT * FROM uv_pfournisseurs  where id ='6330'
--select *  from se_ACH_FOURNISSEUR
--select * from &erp.ach_foursoc


INSERT INTO se_ACH_FOURSOC
 (SELECT '01' soc_cod,
         '01' ets_cod,
         'ACH' service_cod,
         NULL prfarr_cod,
         Decode(TYPIMPUT,'F','FRS_FR','C','FRS_CE','I','FRS_EX','FRS_FR') prfcompta_cod,
         'F' port_cod,
         NULL destana_cod,
         NULL cptcompte_no,
         Decode(STRAIT,'O',ID,NULL) unfab_cod,
         NULL postana_cod,
         depot depot_cod,
         prefiban prefiban_no,
         COMPLRIB rib_no,
         bic bic_no,
         'N' reaautoofstrait_ok,
         NULL delailiv_nb,
         NULL delaitech_nb,
         NULL delaitrp_nb,
         'N' enlevementcli_ok,
         clifourn client_cod,
         NULL cpt_soc_cod,
         NULL cpt_ets_cod,
         NULL orgafin_cod,
         '01' orgafin_soc_cod,
         NULL orgafin_ets_cod,
         0 enreg_id,
         devise devise_cod,
         villebq vilbanque_lib,
         CODBANQUE domibanque_lib,
         NULL plafondcde_val,
         'D' regimetva_cod,
         ID fournisseur_cod,
         MODPAI modpai_cod,
         MODREG modreg_cod,
         1 foursoc_rang,
         'N' enreg_s,
         NULL consolidation_cptable_cod,
         'N' dads_ok,
         NULL axe_analytique_cod,
         NULL niveau_analytique_cod,
         'N' condition_groupe_ok,
         1 taxparafisc_coef,
         NULL seuil_franco_val,
         NULL seuil_cde_val,
         'N' transit_ok,
         NULL traitgeneetiq_typ,
         'N' idemetsref_ok,
         'O' jlivlu_ok,'O' jlivma_ok,'O' jlivme_ok,'O' jlivje_ok,'O' jlivve_ok,'N' jlivsa_ok, null ouv1_heur,null  ferm1_heur,null  ouv2_heur,null  ferm2_heur
    FROM uv_pfournisseurs fuv) ;

 UPDATE se_ach_foursoc tf
 SET enreg_id = (select enreg_id FROM &erp.ach_foursoc f WHERE f.fournisseur_cod=tf.fournisseur_cod )
 WHERE EXISTS (SELECT 1 FROM &erp.ach_foursoc f WHERE f.fournisseur_cod=tf.fournisseur_cod);

 UPDATE se_ach_foursoc SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;




 commit;
